<?php

Class DWC {

	private $record, $record_orig, $records, $total;
	public $db, $_test;
	
	function __construct() {
		$this->_test = new Test();
	}
	
/**
* Returns a since field value
* @return mixed
*/		
	public function get( $field ) {
				
		if (isset($this->record[$field])) {
			return( $this->record[$field] );
		} else {
			return( false );
		}
	}

/**
* Set the value to a field
* @return bool
*/		
	public function set( $field, $value ) {
	
		$this->record[$field] = $value;
		return( true );
		
	}

/**
* Sets the filtered data to the record array
* @param mixed : $data : array of filtered inputs
* @return bool
*/

	public function setData($data) {

		if(count($data)) {
			foreach($data as $key => $value) {
				$this->record[$key] = $value;
			}
		}
		return( true );
	}
 
/**
* Returns all values for project
* @return array
*/		
	public function get_all( $set = 'NEW' ) {

		if ($set == 'NEW') {
			return( $this->record );
		}

		if ($set == 'ORIG') {
			return( $this->record_orig );
		}
		
		return( false );
		
	}

	/**
 	* Get the total record count of the last executed select query
 	*/
	public function getTotal() {
		return $this->total;
	}

	/**
	* Loads the dwc by id
	*/		
	public function load_by_id($Id) {
		$query = sprintf("SELECT * FROM `dwc_record` WHERE `Id` = %s ", mysql_escape_string($Id) );
		$ret = $this->db->query_one( $query );
		unset($this->records);
		if ($ret != NULL) {
			$records = array();
			foreach( $ret as $field => $value ) {
			
				$this->record_orig[$field] = $value;
				$this->record[$field] = $value;
				
			}
			$this->records[] = $this->record;
			return(true);
		} else {
			return(false);
		}
	}

	public function save(){
		if($this->recordExists($this->get('Id'))){
			$query = sprintf(" UPDATE `dwc_record` SET `GlobalUniqueIdentifier` = '%s', `land_id` = '%s', `DateLastModified` = now(), `BasisOfRecord` = '%s', `InstitutionCode` = '%s', `CollectionCode` = '%s', `CatalogNumber` = '%s', `InformationWithheld` = '%s', `Remarks` = '%s', `ScientificName` = '%s', `HigherTaxon` = '%s', `Kingdom` = '%s', `Phylum` = '%s', `Class` = '%s', `Order_` = '%s', `Family` = '%s', `Genus` = '%s', `SpecificEpithet` = '%s', `InfraspecificRank` = '%s', `InfraspecificEpithet` = '%s', `AuthorYearOfScientificName` = '%s', `NomenclaturalCode` = '%s', `IdentificationQualifier` = '%s', `HigherGeography` = '%s', `Continent` = '%s', `WaterBody` = '%s', `IslandGroup` = '%s', `Island` = '%s', `Country` = '%s', `StateProvince` = '%s', `County` = '%s', `Locality` = '%s', `MinimumElevationInMeters` = '%s', `MaximumElevationInMeters` = '%s', `MinimumDepthInMeters` = '%s', `MaximumDepthInMeters` = '%s', `CollectingMethod` = '%s', `ValidDistributionFlag` = '%s', `EarliestDateCollected` = '%s', `LatestDateCollected` = '%s', `DayOfYear` = '%s', `Collector` = '%s', `Sex` = '%s', `LifeStage` = '%s', `Attributes` = '%s', `ImageURL` = '%s', `RelatedInformation` = '%s', `DecimalLatitude` = '%s', `DecimalLongitude` = '%s', `GeodeticDatum` = '%s', `CoordinateUncertaintyInMeters` = '%s', `PointRadiusSpatialFit` = '%s', `FootprintWKT` = '%s', `FootprintSpatialFit` = '%s', `VerbatimCoordinates` = '%s', `VerbatimLatitude` = '%s', `VerbatimLongitude` = '%s', `VerbatimCoordinateSystem` = '%s', `GeoreferenceProtocol` = '%s', `GeoreferenceSources` = '%s', `GeoreferenceVerificationStatus` = '%s', `GeoreferenceRemarks` = '%s', `CatalogNumberNumeric` = '%s', `IdentifiedBy` = '%s', `DateIdentified` = '%s', `CollectorNumber` = '%s', `FieldNumber` = '%s', `FieldNotes` = '%s', `VerbatimCollectingDate` = '%s', `VerbatimElevation` = '%s', `VerbatimDepth` = '%s', `Preparations` = '%s', `TypeStatus` = '%s', `GenBankNumber` = '%s', `OtherCatalogNumbers` = '%s', `RelatedCatalogedItems` = '%s', `Disposition` = '%s', `IndividualCount` = '%s' WHERE `Id` = %s "
			, $this->get('GlobalUniqueIdentifier')
			, $this->get('land_id')
			, $this->get('BasisOfRecord')
			, $this->get('InstitutionCode')
			, $this->get('CollectionCode')
			, $this->get('CatalogNumber')
			, $this->get('InformationWithheld')
			, $this->get('Remarks')
			, $this->get('ScientificName')
			, $this->get('HigherTaxon')
			, $this->get('Kingdom')
			, $this->get('Phylum')
			, $this->get('Class')
			, $this->get('Order_')
			, $this->get('Family')
			, $this->get('Genus')
			, $this->get('SpecificEpithet')
			, $this->get('InfraspecificRank')
			, $this->get('InfraspecificEpithet')
			, $this->get('AuthorYearOfScientificName')
			, $this->get('NomenclaturalCode')
			, $this->get('IdentificationQualifier')
			, $this->get('HigherGeography')
			, $this->get('Continent')
			, $this->get('WaterBody')
			, $this->get('IslandGroup')
			, $this->get('Island')
			, $this->get('Country')
			, $this->get('StateProvince')
			, $this->get('County')
			, $this->get('Locality')
			, $this->get('MinimumElevationInMeters')
			, $this->get('MaximumElevationInMeters')
			, $this->get('MinimumDepthInMeters')
			, $this->get('MaximumDepthInMeters')
			, $this->get('CollectingMethod')
			, $this->get('ValidDistributionFlag')
			, $this->get('EarliestDateCollected')
			, $this->get('LatestDateCollected')
			, $this->get('DayOfYear')
			, $this->get('Collector')
			, $this->get('Sex')
			, $this->get('LifeStage')
			, $this->get('Attributes')
			, $this->get('ImageURL')
			, $this->get('RelatedInformation')
			, $this->get('DecimalLatitude')
			, $this->get('DecimalLongitude')
			, $this->get('GeodeticDatum')
			, $this->get('CoordinateUncertaintyInMeters')
			, $this->get('PointRadiusSpatialFit')
			, $this->get('FootprintWKT')
			, $this->get('FootprintSpatialFit')
			, $this->get('VerbatimCoordinates')
			, $this->get('VerbatimLatitude')
			, $this->get('VerbatimLongitude')
			, $this->get('VerbatimCoordinateSystem')
			, $this->get('GeoreferenceProtocol')
			, $this->get('GeoreferenceSources')
			, $this->get('GeoreferenceVerificationStatus')
			, $this->get('GeoreferenceRemarks')
			, $this->get('CatalogNumberNumeric')
			, $this->get('IdentifiedBy')
			, $this->get('DateIdentified')
			, $this->get('CollectorNumber')
			, $this->get('FieldNumber')
			, $this->get('FieldNotes')
			, $this->get('VerbatimCollectingDate')
			, $this->get('VerbatimElevation')
			, $this->get('VerbatimDepth')
			, $this->get('Preparations')
			, $this->get('TypeStatus')
			, $this->get('GenBankNumber')
			, $this->get('OtherCatalogNumbers')
			, $this->get('RelatedCatalogedItems')
			, $this->get('Disposition')
			, $this->get('IndividualCount')
			, $this->get('Id')
			);

		} else {
			$query = sprintf("INSERT INTO `dwc_record` SET `GlobalUniqueIdentifier` = '%s', `land_id` = '%s', `DateLastModified` = now(), `BasisOfRecord` = '%s', `InstitutionCode` = '%s', `CollectionCode` = '%s', `CatalogNumber` = '%s', `InformationWithheld` = '%s', `Remarks` = '%s', `ScientificName` = '%s', `HigherTaxon` = '%s', `Kingdom` = '%s', `Phylum` = '%s', `Class` = '%s', `Order_` = '%s', `Family` = '%s', `Genus` = '%s', `SpecificEpithet` = '%s', `InfraspecificRank` = '%s', `InfraspecificEpithet` = '%s', `AuthorYearOfScientificName` = '%s', `NomenclaturalCode` = '%s', `IdentificationQualifier` = '%s', `HigherGeography` = '%s', `Continent` = '%s', `WaterBody` = '%s', `IslandGroup` = '%s', `Island` = '%s', `Country` = '%s', `StateProvince` = '%s', `County` = '%s', `Locality` = '%s', `MinimumElevationInMeters` = '%s', `MaximumElevationInMeters` = '%s', `MinimumDepthInMeters` = '%s', `MaximumDepthInMeters` = '%s', `CollectingMethod` = '%s', `ValidDistributionFlag` = '%s', `EarliestDateCollected` = '%s', `LatestDateCollected` = '%s', `DayOfYear` = '%s', `Collector` = '%s', `Sex` = '%s', `LifeStage` = '%s', `Attributes` = '%s', `ImageURL` = '%s', `RelatedInformation` = '%s', `DecimalLatitude` = '%s', `DecimalLongitude` = '%s', `GeodeticDatum` = '%s', `CoordinateUncertaintyInMeters` = '%s', `PointRadiusSpatialFit` = '%s', `FootprintWKT` = '%s', `FootprintSpatialFit` = '%s', `VerbatimCoordinates` = '%s', `VerbatimLatitude` = '%s', `VerbatimLongitude` = '%s', `VerbatimCoordinateSystem` = '%s', `GeoreferenceProtocol` = '%s', `GeoreferenceSources` = '%s', `GeoreferenceVerificationStatus` = '%s', `GeoreferenceRemarks` = '%s', `CatalogNumberNumeric` = '%s', `IdentifiedBy` = '%s', `DateIdentified` = '%s', `CollectorNumber` = '%s', `FieldNumber` = '%s', `FieldNotes` = '%s', `VerbatimCollectingDate` = '%s', `VerbatimElevation` = '%s', `VerbatimDepth` = '%s', `Preparations` = '%s', `TypeStatus` = '%s', `GenBankNumber` = '%s', `OtherCatalogNumbers` = '%s', `RelatedCatalogedItems` = '%s', `Disposition` = '%s', `IndividualCount` = '%s' "
			, $this->get('GlobalUniqueIdentifier')
			, $this->get('land_id')
			, $this->get('BasisOfRecord')
			, $this->get('InstitutionCode')
			, $this->get('CollectionCode')
			, $this->get('CatalogNumber')
			, $this->get('InformationWithheld')
			, $this->get('Remarks')
			, $this->get('ScientificName')
			, $this->get('HigherTaxon')
			, $this->get('Kingdom')
			, $this->get('Phylum')
			, $this->get('Class')
			, $this->get('Order_')
			, $this->get('Family')
			, $this->get('Genus')
			, $this->get('SpecificEpithet')
			, $this->get('InfraspecificRank')
			, $this->get('InfraspecificEpithet')
			, $this->get('AuthorYearOfScientificName')
			, $this->get('NomenclaturalCode')
			, $this->get('IdentificationQualifier')
			, $this->get('HigherGeography')
			, $this->get('Continent')
			, $this->get('WaterBody')
			, $this->get('IslandGroup')
			, $this->get('Island')
			, $this->get('Country')
			, $this->get('StateProvince')
			, $this->get('County')
			, $this->get('Locality')
			, $this->get('MinimumElevationInMeters')
			, $this->get('MaximumElevationInMeters')
			, $this->get('MinimumDepthInMeters')
			, $this->get('MaximumDepthInMeters')
			, $this->get('CollectingMethod')
			, $this->get('ValidDistributionFlag')
			, $this->get('EarliestDateCollected')
			, $this->get('LatestDateCollected')
			, $this->get('DayOfYear')
			, $this->get('Collector')
			, $this->get('Sex')
			, $this->get('LifeStage')
			, $this->get('Attributes')
			, $this->get('ImageURL')
			, $this->get('RelatedInformation')
			, $this->get('DecimalLatitude')
			, $this->get('DecimalLongitude')
			, $this->get('GeodeticDatum')
			, $this->get('CoordinateUncertaintyInMeters')
			, $this->get('PointRadiusSpatialFit')
			, $this->get('FootprintWKT')
			, $this->get('FootprintSpatialFit')
			, $this->get('VerbatimCoordinates')
			, $this->get('VerbatimLatitude')
			, $this->get('VerbatimLongitude')
			, $this->get('VerbatimCoordinateSystem')
			, $this->get('GeoreferenceProtocol')
			, $this->get('GeoreferenceSources')
			, $this->get('GeoreferenceVerificationStatus')
			, $this->get('GeoreferenceRemarks')
			, $this->get('CatalogNumberNumeric')
			, $this->get('IdentifiedBy')
			, $this->get('DateIdentified')
			, $this->get('CollectorNumber')
			, $this->get('FieldNumber')
			, $this->get('FieldNotes')
			, $this->get('VerbatimCollectingDate')
			, $this->get('VerbatimElevation')
			, $this->get('VerbatimDepth')
			, $this->get('Preparations')
			, $this->get('TypeStatus')
			, $this->get('GenBankNumber')
			, $this->get('OtherCatalogNumbers')
			, $this->get('RelatedCatalogedItems')
			, $this->get('Disposition')
			, $this->get('IndividualCount')
			);
		}
		try {
			$result = $this->db->query($query);
		} catch (Exception $e) {
			trigger_error($e->getMessage(),E_USER_ERROR);
		}
		return ( $result );

	}

	/**
	* checks whether record exists in database
	*/
	public function recordExists ($Id){

		$query = sprintf("SELECT `Id` FROM `dwc_record` WHERE `Id` = '%s';", mysql_escape_string($Id) );
		$ret = $this->db->query_one( $query );
		if ($ret == NULL) {
			return false;
		} else {
			return true;
		}
	}

	public function removeSpecies( $species_id, $user_id = -1 ) {
		$query = sprintf("DELETE d FROM dwc_record d JOIN user_land u ON d.land_id = u.land_id AND u.user_id = %s WHERE ID = %s"
			,	mysql_escape_string($user_id)
			,	mysql_escape_string($species_id)
		);

#		print $query;
		$record = $this->db->query($query);
		if($this->db->affected_rows > 0) {
			return true;
		}
		return false;
	}

/**
 * Checks if records exists in the DB
 */
	public function checkRecord($filter) {
		$where = build_where($filter);
		if($where!=''){
			$query = "SELECT ID FROM `dwc_record` WHERE " . $where;
			$record = $this->db->query_one($query);
			if(!is_null($record)) return $record;
		}
		return false;
	}

	public function getList (){
		$query = sprintf("SELECT `Id`, `%s` as value FROM `dwc_record` WHERE `%s` LIKE '%s%%' ORDER BY `%s` %s "
			, mysql_escape_string($this->get('field'))
			, mysql_escape_string($this->get('field'))
			, mysql_escape_string($this->get('value'))
			, mysql_escape_string($this->get('field'))
			, mysql_escape_string($this->get('sort'))
		);
		if( $this->get('start') && $this->get('limit') ) {
			$where = sprintf(" LIMIT %s, %s"
				,mysql_escape_string($this->get('start'))
				,mysql_escape_string($this->get('limit'))
				);
			$query .= $where;
		}

		$ret = $this->db->query_all($query);
		$this->total = $this->db->query_total();		
		return $ret;

	}

	public function listRecords ( $columns = "*" ){
		if($this->get('filter')) {
			$where = build_simple_where( $this->get('filter'), 'dwc_record.', $this->getValidFields() );
		}
		if($this->get('filter')) {
			$where .= build_simple_where( $this->get('filter'), 'land.', array("title"));
		}
		$where .= " GROUP BY dwc_record.ScientificName ";
		if($this->get('order')) {
			$where .= build_order( $this->get('order'), 'dwc_record.', $this->getValidFields() );
		}
		$where .= build_limit( $this->get('start'), $this->get('limit') );
		if($columns == "*") {
				$columns = " dwc_record.* ";
		} else {
				$ar = explode(',',$columns);
				$new_ar = array();
				if(count($ar)) {
						foreach($ar as $col) {
								$validcolumns = $this->getValidFields();
								$new_ar[] = in_array($col,$validcolumns)? 'dwc_record.' . $col : $col ;
						}
						$columns = implode(',',$new_ar);
				}
		}

		$query = sprintf("SELECT SQL_CALC_FOUND_ROWS land.title as title, %s FROM land, users, user_land, dwc_record WHERE users.id = user_land.user_id AND user_land.land_id = land.land_id AND dwc_record.land_id = user_land.land_id ", $columns);

		if($this->get('userFilter') == 'true' && $this->get('user_id') != '')
			$query .=  sprintf(" AND users.id = %s ", $this->get('user_id'));
			
		$query .= $where;

		try {
			$ret = $this->db->query_all( $query );
			$this->total = $this->db->query_total();
		} catch (Exception $e) {
			trigger_error($e->getMessage(),E_USER_ERROR);
		}
		
		return $ret;
	}


	/**
	 * Loads the Check List Region results
	 * @return boolean
	 */
	public function loadCheckListRegion() {
		unset($this->records);
		$this->nodes = '';
		$this->query = '';
		$this->output = '';
		$this->cache = false;

		// No Values sent so send null set.
		if (count(json_decode($this->get('values'))) == 0) {
			$this->output = array();
			$this->type = 1;
		} else {
			$this->query = " SELECT SQL_CALC_FOUND_ROWS ScientificName ";
			foreach( json_decode($this->get('values'), true) as $value ) {
				$this->query .= " , SUM( IF( land_id = {$value['nodeValue']}, 1, 0 ) ) AS R" . ($value['nodeValue'] - 1);
			}
			$this->query .= " FROM `dwc_record` WHERE 1=1 ";
			if($this->get('filter')) {
				$this->query .= build_simple_where( $this->get('filter'), '', $this->getValidFields());
			}
			$this->query .= " GROUP BY ScientificName ";
			if ($this->get('sort') != "") {
				$this->query .= " ORDER BY " . stripslashes($this->get('sort')) . " " . stripslashes($this->get('dir')) . ", ScientificName ";
			} else {
				$this->query .= " ORDER BY ScientificName";
			}
			switch($this->get('output')) {
				case 'json':
					$this->query .= " LIMIT " . stripslashes($this->get('start')) . ", " . stripslashes($this->get('limit'));
					break;
				case 'csv':
				case 'email':
					$this->query .= " LIMIT 1000";	
					break;
			}
		}
		try {
			$this->output = $this->db->query_all($this->query);
		} catch (Exception $e) {
			trigger_error($e->getMessage(),E_USER_ERROR);
		}
		$this->total = $this->db->query_total();
		return(true);
	}

	/**
	 * @param mixed $lists
	 * @return boolean
	 */
    public function finished($lists) {
			foreach($lists as $list) {
				if ($list['index'] < count($list['list']))
					return( false );
			}
			return(true);
    }

    public function getJSON ($cmd = 'checklist-region') {
			switch($cmd) {
				case 'checklist-event':
				case 'checklist-region':
						$this->time_end = microtime(true);
						$time = $this->time_end - $this->time_start;
						$time = number_format($time,4);
						$ar = array();
						$ar['success'] = true;
/*                $ar['cache'] = $this->cache;
						($this->cache) ? $ar['cache_key'] = $this->_cache->CACHE_KEY : '';*/
						$ar['total_execute_time'] = $time;
						$ar['totalCount'] = $this->total;
						$ar['records'] = $this->output;
						return json_encode($ar);
						break;
		    case 'species-land':
 					return json_encode($this->output);
					break;
			}
    }

/**
 * Loads the Check List Region results
 * @return boolean
 */
    public function loadCheckListEvent1() {

        unset($this->records);
        $this->nodes = '';
        $this->query = '';
        $this->output = array();
        $this->cache = false;

        // No Values sent so send null set.
        if (count(json_decode($this->get('values'))) == 0) {
            $this->output = array();
            $this->type = 1;
        } else {
            $this->query = "SELECT SQL_CALC_FOUND_ROWS ";
            $this->query .= " event_type_id ";
            $this->query .= ", count(*) as event_count FROM `event` WHERE 1=1 AND ";
            if ($this->get('filters') != "") {
                $data = json_decode($this->get('filters'), true);
                foreach( $data as $key => $value ) {
                    if ($value != '')
                        $this->query .= sprintf(" %s = '%s' AND ", mysql_escape_string($key), mysql_escape_string($value) );
                }
            }
            if($this->get('start_date') != "") {
                $this->query .= sprintf(" DATE(date_start) >= '%s' AND DATE(date_end) <= '%s' AND ", mysql_escape_string($this->get('start_date')), mysql_escape_string($this->get('end_date')) );
            }

            $this->query .= "%WHERE%";
            $this->query .= " GROUP BY `event_type_id` ";
            if ($this->get('sort') != "") {
                $this->query .= " ORDER BY " . stripslashes($this->get('sort')) . " " . stripslashes($this->get('dir')) . ", `event_type_id` ";
            } else {
                $this->query .= " ORDER BY `event_type_id`";
            }
            switch($this->get('output')) {
                case 'json':
                    $this->query .= " LIMIT " . stripslashes($this->get('start')) . ", " . stripslashes($this->get('limit'));
                    break;
                case 'csv':
                case 'email':
                    $this->query .= " LIMIT 1000";	
                    break;
            }
            $lists = array();
            $master = array();
            foreach( json_decode($this->get('values'), true) as $value ) {
                $where = sprintf(" %s = '%s' ", mysql_escape_string($value['nodeApi']), mysql_escape_string($value['nodeValue']) );
                $tmpQuery = str_replace('%WHERE%', $where, $this->query);

                $nodes = array();
// print '<br>'.$tmpQuery;
                try {
                    $records = $this->db->query_all($tmpQuery);
                } catch (Exception $e) {
                    trigger_error($e->getMessage(),E_USER_ERROR);
                }

                if(count($records)) {
                    foreach ($records as $record) {
                        switch($this->get('output')) {
                            case 'json':
                            case 'csv':
                            case 'email':
                                $nodes[] = $record;
                                break;
                        }
                    }
                }
                $lists[] = array('index'=>0, 'list'=>$nodes);
            }

            while (!$this->finished($lists)) {
                unset($row);
                // This gets the first cell of each list based on the current index.
                foreach( $lists as $name => $list ) {
                    $index = $list['index'];
                    if ( $index < count($list['list']) ) {
                        $cell = $list['list'][$index];
                            $tmpEventType = ( $cell->event_type_id == '' ) ? 0 : $cell->event_type_id;
                            $row[$name] = $tmpEventType;
                    } else {
                            $row[$name] = "EOF";
                    }
                }
                asort($row); // Sorts each row returned in alpha order

                // This looks for matches based on the smallest alpha item and increments the indexes found.
                $first = true;
                unset($fill);
                foreach( $row as $key => $value ) {
                    if ($value == "EOF") {
                            $fill[] = $key;
                    } else {
                        // If this is the first item in the row set as the master
                        if ($first) {
                            $master = $value;
                            $first = false;
                            $tmpIndex = $lists[$key]['index'];
                            $tmpCell = $lists[$key]['list'][$tmpIndex];
                             $this->output[] = array(
                                  'Event Type'=>$tmpCell->event_type_id
                            );
                       }
                        if ($master == $value) {
                            $tmpIndex = $lists[$key]['index'];
                            $tmpCell = $lists[$key]['list'][$tmpIndex];
                            $this->output[ count($this->output)-1 ]['R' . $key] = $tmpCell->event_count;
                            $lists[$key]['index']++;
                        } else {
                            $this->output[ count($this->output)-1 ]['R' . $key] = 0;
                        }
                    }
                }

                if (count($fill) > 0) {
                    foreach( $fill as $key) {
                // $this->output[ count($output)-1 ]['R' . $key] = 0;
                    }
                }
            }
        }
        return true;
    }

/**
 * Loads the Check List Region results
 * @return boolean
 */
    public function loadCheckListEvent() {

        unset($this->records);
        $this->nodes = '';
        $this->query = '';
        $this->output = array();
        $this->cache = false;

        // No Values sent so send null set.
        if (count(json_decode($this->get('values'))) == 0) {
            $this->output = array();
            $this->type = 1;
        } else {

            $this->query = "SELECT SQL_CALC_FOUND_ROWS event_type.event_type_id, event_type.title ";
            foreach( json_decode($this->get('values'), true) as $value ) {
                $this->query .= " , SUM( IF( event.land_id = {$value['nodeValue']}, 1, 0 ) ) AS R" . ($value['nodeValue'] - 1);
            }
            $this->query .= " FROM event, event_type WHERE event.event_type_id = event_type.event_type_id ";
            if ($this->get('filters') != "") {
                $data = json_decode($this->get('filters'), true);
                foreach( $data as $key => $value ) {
                    if ($value != '')
                        $this->query .= sprintf(" AND %s = '%s' ", mysql_escape_string($key), mysql_escape_string($value) );
                }
            }

		if(count(json_decode($this->get('filter')))) {
			$where = build_where(json_decode($this->get('filter'), true), 'event.', $this->getValidEventFields());
		}
		if ($where != '') {
			$where = " AND " . $where;
		}
		$this->query .= $where;

            if($this->get('start_date') != "") {
                $this->query .= sprintf(" AND DATE(date_start) >= '%s' AND DATE(date_end) <= '%s' AND ", mysql_escape_string($this->get('start_date')), mysql_escape_string($this->get('end_date')) );
            }

            $this->query .= " GROUP BY `event_type_id` ";
            if ($this->get('sort') != "") {
                $this->query .= " ORDER BY " . stripslashes($this->get('sort')) . " " . stripslashes($this->get('dir')) . ", `event_type_id` ";
            } else {
                $this->query .= " ORDER BY `event_type_id`";
            }
            switch($this->get('output')) {
                case 'json':
                    $this->query .= " LIMIT " . stripslashes($this->get('start')) . ", " . stripslashes($this->get('limit'));
                    break;
                case 'csv':
                case 'email':
                    $this->query .= " LIMIT 1000";	
                    break;
            }
// print $this->query;
					try {
						$this->output = $this->db->query_all($this->query);
						$this->total = $this->db->query_total();
						if ($this->total == 0) {
							$this->output = array();
						}
					} catch (Exception $e) {
						trigger_error($e->getMessage(),E_USER_ERROR);
					}
        }
        return true;
    }

        public function getValidFields() {
            $ar = array( 'Id', 'GlobalUniqueIdentifier', 'land_id', 'DateLastModified', 'BasisOfRecord', 'InstitutionCode', 'CollectionCode', 'CatalogNumber', 'InformationWithheld', 'Remarks', 'ScientificName', 'HigherTaxon', 'Kingdom', 'Phylum', 'Class', 'Order_', 'Family', 'Genus', 'SpecificEpithet', 'InfraspecificRank', 'InfraspecificEpithet', 'AuthorYearOfScientificName', 'NomenclaturalCode', 'IdentificationQualifier', 'HigherGeography', 'Continent', 'WaterBody', 'IslandGroup', 'Island', 'Country', 'StateProvince', 'County', 'Locality', 'MinimumElevationInMeters', 'MaximumElevationInMeters', 'MinimumDepthInMeters', 'MaximumDepthInMeters', 'CollectingMethod', 'ValidDistributionFlag', 'EarliestDateCollected', 'LatestDateCollected', 'DayOfYear', 'Collector', 'Sex', 'LifeStage', 'Attributes', 'ImageURL', 'RelatedInformation', 'DecimalLatitude', 'DecimalLongitude', 'GeodeticDatum', 'CoordinateUncertaintyInMeters', 'PointRadiusSpatialFit', 'FootprintWKT', 'FootprintSpatialFit', 'VerbatimCoordinates', 'VerbatimLatitude', 'VerbatimLongitude', 'VerbatimCoordinateSystem', 'GeoreferenceProtocol', 'GeoreferenceSources', 'GeoreferenceVerificationStatus', 'GeoreferenceRemarks', 'CatalogNumberNumeric', 'IdentifiedBy', 'DateIdentified', 'CollectorNumber', 'FieldNumber', 'FieldNotes', 'VerbatimCollectingDate', 'VerbatimElevation', 'VerbatimDepth', 'Preparations', 'TypeStatus', 'GenBankNumber', 'OtherCatalogNumbers', 'RelatedCatalogedItems', 'Disposition', 'IndividualCount' );
            return $ar;
        }

	public function getValidEventFields() {
			$ar = array( 'title', 'event_type_id', 'date_start', 'date_end', 'land_id');
			return $ar;
	}

	/**
	 * Gets the species count with land
	 */
	public function getSpeciesLand($total_count) {
		$op = array();
		// Only returns records if species exist on land
		$query = 'SELECT count( * ) ct, d.land_id, l.title FROM dwc_record d, land l WHERE l.land_id = d.land_id GROUP BY d.land_id ORDER BY l.title';
		$res = $this->db->query_all($query);
		if(count($res)) {
			foreach($res as $val) {
				$op[] = array('land_id' => $val->land_id, 'land_name' => $val->title, 'x_species' => $val->ct, 'percent' => ($val->ct/$total_count)*100, 'totalSpecies' => $total_count);
			}
		}

		$output = array();
		$output['success'] = true;
		$output['totalSpecies'] = $total_count;
		$output['records'] = $op;
		$this->output = $output;
		return true;
	}

	/**
	 * getTotalSpecies
	 * get Total count of distinct species (Scientific Names)
	 */
	public function getTotalSpecies () {
		$query = 'select count(DISTINCT(ScientificName)) ct FROM dwc_record';
		$res = $this->db->query_one($query);
		return $res->ct;
	}

}

?>
